<?php

class Lookupmodel extends CI_Model {

	function Lookupmodel() {
		// Call the Model constructor
		parent::__construct();
	}
	
	function GetPropertyType($LanguageId, $market) {
		$this->db->select();
		$this->db->from('PropertyType');
		
		$where = array('LanguageFID' => $LanguageId, 'MarketFID' => $market);
		$this->db->where($where);

		$query = $this->db->get();

		return $query->result();
	}
	
	function getZoning($LanguageId, $market, $type) {
		$this->db->select();
		$this->db->from('dbo.ListingOption');
		
		$where = array(
			'LanguageFID' 	=> $LanguageId, 
			'MarketFID' 	=> $this->db->escape_str($market),
			'Type'			=> $this->db->escape_str($type)
		);
		$this->db->where($where);
		
		$this->db->order_by('Name');

		$query = $this->db->get();
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	function GetPropertyCategory($LanguageId, $marketCode) {
		$this->db->select('DISTINCT Type');
		$this->db->from('PropertyType');
                $where = array('LanguageFID' => $LanguageId,'MarketFID' => $marketCode);
		$this->db->where($where);
		$this->db->order_by('Type DESC');

		$query = $this->db->get();

		return $query->result();
	}

	function getPropertyTypeByType($LanguageId, $propertyClassType, $market) {
		$this->db->select();
		$this->db->from('PropertyType');
		$where = array('LanguageFID' => $LanguageId, 'Type' => $propertyClassType, 'MarketFID' => $market);
		$this->db->where($where);

		$query = $this->db->get();

		return $query->result();
	}

	function GetLocation($LanguageId) {
		$this->db->select();
		$this->db->from('Location');
		$this->db->where('LanguageFID', $LanguageId);

		$query = $this->db->get();
		return $query->result();
	}

	function GetCountry($LanguageId) {
		if ($LanguageId == 1) {
			$this->db->select('Country_ID, Country_EN AS CountryName');
		} else {
			$this->db->select('Country_ID, Country_CN AS CountryName');
		}
		$this->db->from('Country');
		$this->db->where('Country_ID !=', 10);

		$query = $this->db->get();
		return $query->result();
	}

	function getCountries($LanguageId) {
		$this->db->select('Country_ID,Name,Code');
		$this->db->from('dbo.Country');
		$where = array (
			'LanguageFID'	=> $LanguageId,
			'Status'	=> 1
		);
		$this->db->where($where);
		$this->db->order_by('Name');
		$query = $this->db->get();

		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	function getCountryId($market) {
		$this->db->select('Country_ID,Name,Code');
		$this->db->from('dbo.Country');
		$where = array (
			'Code'	=> $this->db->escape_str($market),
			'Status'=> 1
		);
		$this->db->where($where);
		$query = $this->db->get();

		if($query->num_rows() > 0) {
			$row = $query->row();
			$country_id = $row->Country_ID;
			return $country_id;
		} else {
			return false;
		}
	}
	
	function getInternationalCountries($LanguageId, $Localorint = 1) {
		$this->db->select('DISTINCT c.Country_ID,c.Name,c.Code');
		$this->db->from('Listings l');
		$this->db->join('dbo.Country c', 'c.Country_ID = l.CountryFID'); // this joins the quote table to the topics table
		
		if($Localorint == 0) {
			$Market = 'ID';
		} else {
			$Market = '<> ID';
		}
		
		$where = array(
			'c.LanguageFID' => $LanguageId,  
			'l.MarketFID' => $Market
		);
		
		$this->db->where($where);
		$this->db->order_by('c.Name');
		
		$query = $this->db->get();

		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	//Get list all cities
	function getListCitiesUsingStoredPro() {
		$tsql_callSP = '{call sp_getListCities(?,?)}';
		$params = array(
		array($this->config->item('market'), SQLSRV_PARAM_IN),
		array($this->config->item('config_language_id'), SQLSRV_PARAM_IN)
		);

		$results = $this->db->exec_query($tsql_callSP, $params);

		$data = array();

		if($results) {
			while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		return $data;
	}

	/*
	 * Author: Chi Nguyen
	 * Get City Information By City Id
	 *
	 * @access public
	 * @param integer
	 * @param integer
	 * @param string
	 * @return array
	 */
	function getCity($LanguageId, $cityId, $market) {
		$this->db->select('c.City_ID,c.Name');
		$this->db->from('dbo.City c');
		$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table
		$where = array('c.LanguageFID' => $LanguageId, 'c.City_ID' => $cityId, 'co.Code' => $market);
		$this->db->where($where);
		$this->db->order_by('c.Name');

		$query = $this->db->get();

		return $query->row();
	}

	/*
	 * Author: Chi Nguyen
	 * Get Location information by location id
	 *
	 * @access public
	 * @param integer
	 * @parma integer
	 * @param string
	 * @return array
	 *
	 */

	function getLocationInfo($LanguageId, $locationId, $market) {
		$this->db->select('l.Location_ID,l.Name');
		$this->db->from('dbo.Location l');
		$this->db->join('dbo.City c', 'c.City_ID = l.CityFID'); // this joins the user table to topics
		$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table
		$where = array (
			'l.Visible'	=> 1,
			'l.LanguageFID'	=> $LanguageId,
			'l.Location_ID' => $locationId,
			'co.Code' 		=> $this->db->escape_str($market)
		);
		$this->db->where($where);
		$this->db->order_by('l.Name');
		$query = $this->db->get();

		return $query->row();
	}

	function getListCities($LanguageId, $market) {
		$this->db->select('c.City_ID,c.Name,c.Code');
		$this->db->from('dbo.City c');
		$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table
		$this->db->where('c.LanguageFID', $LanguageId, 'co.Code', $market);
		
		$where = array (
			'c.LanguageFID'	=> $LanguageId,
			'co.Code' 		=> $this->db->escape_str($market)
		);
		$this->db->where($where);
		
		$this->db->order_by('c.Name');
		$query = $this->db->get();
		return $query->result();
	}

	function getListLocation($LanguageId, $market) {
		$this->db->select('l.Location_ID,l.Name,l.CityFID,l.LanguageFID');
		$this->db->from('dbo.Location l');
		$this->db->join('dbo.City c', 'l.CityFID = c.City_ID'); // this joins the user table to topics
		$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table
		$where = array (
			'l.Visible'		=> 1,
			'l.LanguageFID' => $LanguageId,
			'co.Code' 		=> $this->db->escape_str($market)
		);
		
		$this->db->where($where);
		$this->db->order_by('l.Name');
		$query = $this->db->get();
		return $query->result();
	}

	/*
	 * Author: Chi Nguyen
	 * get list all location by city id
	 *
	 * @access public
	 * @param integer
	 * @param integer
	 * @param string
	 * @return array
	 */
	function getLocationByCityId($LanguageId, $CityId, $market) {
		if($CityId == 1) { //is Bali
			$locationId = 1;
			return $this->getSubAreaByLocationId($LanguageId, $locationId, $market);
		} else {
			$this->db->select('DISTINCT l.Location_ID,l.Name,l.CityFID,l.LanguageFID');
			$this->db->from('dbo.Location l');
			$this->db->join('dbo.City c', 'l.CityFID = c.City_ID'); // this joins the user table to topics
			$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table
			$where = array(
				'l.Visible' 	=> 1, 
				'l.LanguageFID' => $LanguageId, 
				'l.CityFID' 	=> $CityId, 
				'co.Code' => 'ID'
			);
			$this->db->where($where);
			$this->db->order_by('l.Name');
				
			$query = $this->db->get();
				
			return $query->result();
		}
		 
	}

	/*
	 * Author: Chi Nguyen
	 * Get List All Sub Area
	 *
	 * @access public
	 * @param integer
	 * @param integer
	 * @return array
	 */
	function getListSubarea($LanguageId, $market) {
		$this->db->select('s.Subarea_ID,s.Name,s.LocationFID,s.LanguageFID');
		$this->db->from('dbo.Subarea s');
		$this->db->join('dbo.Location l', 'l.Location_ID = s.LocationFID');
		$this->db->join('dbo.City c', 'l.CityFID = c.City_ID'); // this joins the user table to topics
		$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table
		$where = array (
			'l.LanguageFID' => $LanguageId,
			'co.Code' 		=> $this->db->escape_str($market)
		);
		
		$this->db->where($where);
		$this->db->order_by('s.Name');
		$query = $this->db->get();
		return $query->result();
	}

	/*
	 * Author: Chi Nguyen
	 * Get List All Sub Area By Location Id
	 *
	 * @access public
	 * @param integer
	 * @param integer
	 * @return array
	 */
	function getSubAreaByLocationId($LanguageId, $locationId, $market) {
		$this->db->select('DISTINCT s.Subarea_ID,s.Name');
		$this->db->from('dbo.Subarea s');
		$this->db->join('dbo.Location l', 'l.Location_ID = s.LocationFID');
		$this->db->join('dbo.City c', 'l.CityFID = c.City_ID'); // this joins the user table to topics
		$this->db->join('dbo.Country co', 'co.Country_ID = c.CountryFID'); // this joins the quote table to the topics table

		$where = array(
			'l.LanguageFID' => $LanguageId,
			'l.Location_ID' => $locationId,
			'co.Code' => $this->db->escape_str($market)
		);

		$this->db->where($where);
		$this->db->order_by('s.Name');
		$query = $this->db->get();
		return $query->result();
	}

	function GetBedroom() {

	}

	function GetFeature($LanguageId) {
		return $this->db->where('LanguageFID', $LanguageId)->get('Features')->result();
	}

	function GetFacilities($LanguageId) {
		return $this->db->where('LanguageFID', $LanguageId)->get('Facilities')->result();
	}

	function GetPrice($Type, $LanguageId) {
		$this->db->where('List_name', $Type);
		$this->db->where('LanguageFID', $LanguageId);
		$query = $this->db->get('PriceList');
		return $query->result();
	}

	//get Country Market from AP database
	function getCountryMarket() {
		//$DB2 = $this->load->database('main', TRUE);

		$tsql_callSP = '{call getCountryMarket(?)}';

		$params = array(
		array(1, SQLSRV_PARAM_IN)
		);

		$results = $this->db->exec_query($tsql_callSP, $params);

		$data = array();

		if($results) {
			while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC)) {
				$data[] = $row;
			}
		}

		return $data;
	}

	function getFeaturedProjects($LanguageId, $market, $projectType = 'Featured') {
		$this->db->select('p.ID, pd.ProjectName,pd.Description');
		$this->db->from('dbo.PrjDirectory p');
		$this->db->join('dbo.PrjDirDescriptions pd', 'p.ID = pd.PrjDirectoryFID'); // this joins the user table to topics
		$where = array(
			'pd.LanguageFID' => $LanguageId, 
			'p.MarketFID' => $this->db->escape_str($market) , 
			'p.ProjectType' => $this->db->escape_str($projectType),
			'p.StatusFID' => 1
		);

		$this->db->where($where);
		$this->db->order_by('pd.ProjectName');

		$query = $this->db->get();
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}
	
	function getFeaturedPropertyListing($LanguageId, $market, $projectType = 'Featured', $mediaType = 'Cover', $theme = 'Landscape') {
		$this->db->select('TOP 10 l.Listing_ID,l.Link,l.ProjectName,l.Description');
		$this->db->from('dbo.viewProjectListing l');

		$where = array(
			'l.LanguageFID' => $LanguageId, 
			'l.MarketFID' => $this->db->escape_str($market) , 
			'l.IsFeatured' => 1
		);

		$this->db->where($where);
		$this->db->order_by('l.ProjectName');

		$query = $this->db->get();
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	function getFeaturedProjectsV2($LanguageId, $market, $projectType = 'Featured', $mediaType = 'Cover', $theme = 'Landscape') {
		$this->db->select('TOP 10 p.ID,p.Link,pd.ProjectName,pd.Description');
		$this->db->from('dbo.PrjDirectory p');
		$this->db->join('dbo.PrjDirDescriptions pd', 'p.ID = pd.PrjDirectoryFID'); // this joins the user table to topics
		$this->db->join('dbo.PrjDirMedia pm', 'pm.PrjDirectoryFID = p.ID');

		$where = array(
			'pd.LanguageFID' => $LanguageId, 
			'p.MarketFID' => $this->db->escape_str($market) , 
			'p.ProjectType' => $this->db->escape_str($projectType),
			'p.StatusFID' => 1,
			'pm.MediaType' => $this->db->escape_str($mediaType),
			'pm.MediaOrientation' => $this->db->escape_str($theme)
		);

		$this->db->where($where);
		$this->db->order_by('pd.ProjectName');

		$query = $this->db->get();
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	function getPremiumProjects($LanguageId, $market, $projectType = 'Premium') {
		$this->db->select('p.ID, pd.ProjectName');
		$this->db->from('dbo.PrjDirectory p');
		$this->db->join('dbo.PrjDirDescriptions pd', 'p.ID = pd.PrjDirectoryFID'); // this joins the user table to topics
		$where = array(
			'pd.LanguageFID' => $LanguageId, 
			'p.MarketFID' => $this->db->escape_str($market) , 
			'p.ProjectType' => $this->db->escape_str($projectType),
			'p.StatusFID' => 1
		);

		$this->db->where($where);
		$this->db->order_by('pd.ProjectName');

		$query = $this->db->get();
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	function getPremiumProjectsV2($LanguageId, $market, $projectType = 'Premium', $mediaType = 'Cover', $theme = 'Landscape') {
		$this->db->select('TOP 10 p.ID,p.Link,pd.ProjectName');
		$this->db->from('dbo.PrjDirectory p');
		$this->db->join('dbo.PrjDirDescriptions pd', 'p.ID = pd.PrjDirectoryFID'); // this joins the user table to topics
		$this->db->join('dbo.PrjDirMedia pm', 'pm.PrjDirectoryFID = p.ID'); // this joins the user table to topics
		$where = array(
			'pd.LanguageFID' => $LanguageId, 
			'p.MarketFID' => $this->db->escape_str($market) , 
			'p.ProjectType' => $this->db->escape_str($projectType),
			'p.StatusFID' => 1,
			'pm.MediaType' => $this->db->escape_str($mediaType),
			'pm.MediaOrientation' => $this->db->escape_str($theme)
		);

		$this->db->where($where);
		$this->db->order_by('pd.ProjectName');

		$query = $this->db->get();
		if($query->num_rows() > 0) {
			return $query->result();
		} else {
			return false;
		}
	}

	/*
	 * Author:Chi Nguyen
	 * Get Tenure Name By Id
	 * access public
	 * param integer
	 * param string
	 * param integer
	 * return array
	 */
	function getTenure($LanguageId, $market, $ListingId) {
		$this->db->select('t.Name');
		$this->db->from('dbo.Tenures t');
		$this->db->join('dbo.Listings l', 'l.TenureFID = t.Tenure_ID'); // this joins the user table to topics
		
		$where = array('l.Listing_ID' => $ListingId, 'l.MarketFID' => $this->db->escape_str($market) , 't.LanguageFID' => $LanguageId);
		$this->db->where($where);

		$query = $this->db->get();

		return $query->row();
	}
	
	function getAllTenure($LanguageId, $market) {
		$this->db->select('t.Tenure_ID,t.Name');
		$this->db->from('dbo.Tenures t');
		
		$where = array('t.MarketFID' => $this->db->escape_str($market) , 't.LanguageFID' => $LanguageId);
		$this->db->where($where);
		$this->db->order_by('t.Name');

		$query = $this->db->get();

		return $query->result();
	}
	
	function getDisclaimer($LanguageId, $market) {
		$this->db->select();
		$this->db->from('dbo.Disclaimer t');
		
		$where = array('t.MarketFID' => $this->db->escape_str($market) , 't.LanguageFID' => $LanguageId);
		
		$this->db->where($where);

		$query = $this->db->get();
		
		if($query->num_rows() > 0) {
			return $query->row(); 
		} else {
			return false;
		}
	}
	
	function getLocationOffice($market) {
		$str = "SELECT * FROM dbo.ContactUsPage t WHERE t.MarketFID = '". $this->db->escape_str($market) ."' AND t.Name <> ''";
		
		$query = $this->db->query($str);

		return $query->result();
	}
	
	function getLanguages($market) {
		$this->db->select('t.Language_ID, t.Name');
		$this->db->from('dbo.Language t');
		
		$where = array(
			't.MarketFID' => $this->db->escape_str($market)
		);
		$this->db->where($where);

		$query = $this->db->get();

		return $query->result();
	}
	
	function getSize() {
		$this->db->select('s.Sizeoption_ID,s.Value,s.Sizeoption');
		$this->db->from('dbo.Size s');
		
		$this->db->order_by('s.Value');

		$query = $this->db->get();

		return $query->result();
	}

	/*
	 * Author: Chi Nguyen
	 * Get theme
	 * access public
	 * param string
	 * return string
	 */
	function getTheme($market) {
		$this->db->select('Value');
		$this->db->from('Setting');
		$where = array(
			'MarketFID' => $this->db->escape_str($market)
		);

		$this->db->where($where);

		$query = $this->db->get();

		if($query->num_rows() > 0) {
			return $query->row();
		} else {
			return false;
		}
	}

}

?>
